﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using You2V.Data.Shared;
using System.Data;
using You2V.LocalDBLayer;
using You2V.Data;

namespace You2V.LocalDBLayer
{
    public class SponsorDBL
    {
        public SponsorDBL()
        {
        }
		public static SponsorDBL Instance = new SponsorDBL();

		#region im table
		public List<Sponsor> GetAllSponsor()
        {
			return GetSponsorQuerry("");
        }

		public Sponsor GetSponsorByStudentID(int StudentID)
        {
            string condition = "";
            if (StudentID > 0)
            {
                condition = string.Format(" WHERE tc_studentid = {0}", StudentID);
            }
            else
            {
                return null;    //StudentID=0应该是还没有提交到后台的状态，没有资助人
            }

			try
			{
				return GetSingleSponsorQuerry(condition);
			}
			catch (ObjectUnmatchedException)
			{
				throw new ObjectUnmatchedException(string.Format("More than 1 sponser found for Student {0}", StudentID));
			}
        }

		private Sponsor GetSponsorBySponsorID(int ID)
        {
            string condition = "";
			if(ID > 0)
			   condition = string.Format(" WHERE tc_id = {0}", ID);
			return GetSingleSponsorQuerry(condition);
        }

		private Sponsor GetSingleSponsorQuerry(string Querry)
		{
			List<Sponsor> SponsorList = GetSponsorQuerry(Querry);
			if (SponsorList.Count == 0)
				return null;
			else if (SponsorList.Count != 1)
				throw new ObjectUnmatchedException(string.Format("More than 1 sponser found for Querry {0}", Querry));

			return SponsorList[0];
		}

		private List<Sponsor> GetSponsorQuerry(string Querry)
        {
			List<Sponsor> SponsorList = new List<Sponsor>();

			string sqlQuery = string.Format("SELECT * FROM im {0}", Querry);
			DataTable dt = DBAccess.GetDataTable(sqlQuery);

			if (dt == null)
			{
				return null;
			}

			foreach (DataRow row in dt.Rows)
			{
				SponsorList.Add(GetSponsorByRow(row));
			}

			return SponsorList;
        }

		private Sponsor GetSponsorByRow(DataRow row)
		{
			Sponsor sponsor = new Sponsor((int)row["tc_id"], (int)row["tc_studentid"]);
			switch ((string)row["tc_male"])
			{
				case "男":
					sponsor.Gender = Gender.Male;
					break;
				case "女":
					sponsor.Gender = Gender.Female;
					break;
				default:
					sponsor.Gender = Gender.NA;
					break;
			}
			//sponsor.GetYou2VFrom = (string)row["tc_how"];
			sponsor.ShortName = (string)row["tc_nam"];
			sponsor.Name = (string)row["tc_call"];
			sponsor.Title = (string)row["tc_ccl"];
			sponsor.Email = (string)row["tc_mail"];
			sponsor.Postcode = (string)row["tc_post"];
			sponsor.Address = (string)row["tc_dz"];
			sponsor.Telephone = (string)row["tc_tel"];
			sponsor.PayPeriod = (string)row["tc_pay"];
			sponsor.PayMethod = (string)row["tc_meo"];
			sponsor.SponsorMessage = (string)row["tc_bz"];
			sponsor.CreateDate = (DateTime)row["tc_date"];
			//sponsor.MSN = (string)row["tc_msn"];
			//sponsor.QQ = (string)row["tc_qq"];

			return sponsor;
		}
		#endregion

		#region stu_spon_linq Table
		
		public Sponsor GetStudentLinkedSponsorFromDB(Guid Student2012ID)
		{
			try
			{
				string sqlQuery = string.Format("SELECT spon_ID FROM stu_spon_linq WHERE stu_ID = {{{0}}}", Student2012ID);
				DataTable dt = DBAccess.GetDataTable(sqlQuery);

				if (dt == null || dt.Rows.Count == 0)
				{
					return null;
				}
				if (dt.Rows.Count != 1)
				{
					throw new ObjectUnmatchedException(string.Format("More than 1 sponsor select for student {0}", Student2012ID));
				}

				return GetSponsorBySponsorID((int)dt.Rows[0]["spon_ID"]);
			}
			catch (Exception ex)
			{ throw ex; }
		}

		public Sponsor CreateStudentSponsorLink(Guid StudentId, int Sponsor)
		{
			Guid linqGuid = Guid.NewGuid();
			string sqlQuery = string.Format("INSERT INTO stu_spon_linq "
				+ "(ID, stu_ID, spon_ID, create_date) VALUES "
				+ "({{{0}}}, {{{1}}}, {2}, '{3}')", Guid.NewGuid(), StudentId, Sponsor, DateTime.Now.ToString());

			DBAccess.RunCommand(sqlQuery);

			//System.Threading.Thread.Sleep(100);
			return GetStudentLinkedSponsorFromDB(StudentId);
		}
		#endregion
	}
}
